<cfquery name="GetShippedOrders" datasource="CSAC">
SELECT     dbo.[Order].Order_ID, dbo.[Order].Deleted AS Order_Deleted, dbo.[Order].Status, dbo.[Order].Order_Date, dbo.[Order].Approved_Date, dbo.[Order].Shipped_Date, 
                      dbo.Order_Product.Order_Product_ID, dbo.Order_Product.Product_ID, dbo.Order_Product.Deleted AS Order_Product_Deleted, dbo.Order_Product.Quantity, 
                      dbo.Order_Product.Quantity_Shipped
FROM         dbo.[Order] INNER JOIN
                      dbo.Order_Product ON dbo.[Order].Order_ID = dbo.Order_Product.Order_ID INNER JOIN
                      dbo.Product ON dbo.Order_Product.Product_ID = dbo.Product.Product_ID
WHERE     (dbo.[Order].Deleted = 0) AND (dbo.Order_Product.Deleted = 0) AND (dbo.[Order].Status = 'Shipped')
</cfquery>

<cfloop query="GetShippedOrders">
<cfif Quantity_Shipped eq 0>
	<cfquery name="UpdateQS" datasource="CSAC">
		update Order_Product
		set Quantity_Shipped = #Quantity#
		where Order_Product_ID = #Order_Product_ID#
	</cfquery>
</cfif>
</cfloop>

<!--- <cfdump var="#GetShippedOrders#"> --->
<h1>Done</h1>


<!--- <cfquery name="GetCSACOrders" datasource="WMS">
	select * from CSAC_Order
</cfquery>

<cfloop query="GetCSACOrders">
	<cfloop index="workshopID" list="#Workshop_IDs#">
		<cfquery name="CheckCSACOrderWorkshop" datasource="WMS">
			select CSAC_Order_Workshop_ID
			from CSAC_Order_Workshop
			where CSAC_Order_ID = #CSAC_Order_ID#
			and Workshop_ID = #workshopID#
		</cfquery>
		<cfif CheckCSACOrderWorkshop.RecordCount eq 0>
			<cfquery name="InsertCSACOrderWorkshop" datasource="WMS">
				insert into CSAC_Order_Workshop
				(Deleted,
				CSAC_Order_ID,
				Workshop_ID)
				values
				(#Deleted#,
				#CSAC_Order_ID#,
				#workshopID#)
			</cfquery>
		<cfelse>
			<cfquery name="UpdateCSACOrderWorkshop" datasource="WMS">
				update CSAC_Order_Workshop
				set Deleted = #Deleted#
				where CSAC_Order_Workshop_ID = #CheckCSACOrderWorkshop.CSAC_Order_Workshop_ID#
			</cfquery>
		</cfif>
	</cfloop>
</cfloop> --->

<!--- <cfquery name="getTracking" datasource="CSAC">
	select * from tracking
</cfquery>

<cfloop query="getTracking">
	<cfquery name="updateOrder" datasource="CSAC">
	 	update [Order]
		set Status = <cfqueryparam cfsqltype="cf_sql_varchar" value="Shipped">
		where Order_ID = #Order_ID#
	</cfquery>
	<cfquery name="updateShippedDate" datasource="CSAC">
	 	update [Order]
		set Shipped_Date = <cfqueryparam cfsqltype="cf_sql_date" value="#Date_Created#">
		where Order_ID = #Order_ID#
		and Shipped_Date is null
	</cfquery>
</cfloop> --->

<!--- <cfquery name="GetCSACOrders" datasource="WMS">
	select *
	from CSAC_Order
</cfquery>

<cfloop query="GetCSACOrders">
	<cfquery name="getOrderWorkshop" datasource="WMS">
	 select Sum(Anticipated_Students) as tap from workshop where Workshop_ID in (#getCSACOrders.Workshop_IDs#)
	</cfquery>
	<cfset tap = getOrderWorkshop.tap />
	<cfquery name="getOrder" datasource="CSAC">
		select *
		from [Order]
		where WMS_Order_ID = #CSAC_Order_ID#
	</cfquery>
	<cfquery name="getOrderProduct" datasource="CSAC">
		select *
		from Order_Product
		where Order_ID = #getOrder.Order_ID#
		and Product_ID = 24
	</cfquery>
	<cfif getOrderProduct.RecordCount gt 0 and getOrderProduct.Quantity gt tap>
		<cfquery name="updateOrderProduct" datasource="CSAC">
			update Order_Product
			set Quantity = #tap#
			where Order_Product_ID = #getOrderProduct.Order_Product_ID#
		</cfquery>
	</cfif>
</cfloop> --->

<!--- <cfquery name="GetOrder51" datasource="CSAC">
	select *
	from Order_51
</cfquery>

<cfloop query="GetOrder51">
<cfquery name="UpdateOrderProduct" datasource="CSAC">
	update Order_Product
	set Quantity = #Quantity#,
	Quantity_Shipped = #Quantity_Shipped#
	where Order_Product_ID = #Order_Product_ID#
</cfquery>
</cfloop>
 --->
<!--- <cfquery name="GetTracking" datasource="CSAC">
	select *
	from Tracking
</cfquery>

<cfloop query="getTracking">
	<cfquery name="UpdateOrder" datasource="CSAC">
		update [Order]
		set Shipped_Date = '#Date_Created#',
		Status = 'Shipped'
		where Order_ID = #Order_ID#
	</cfquery>
</cfloop>

 --->
<!--- 
Rename product files, images and thumbnails to correspond to Product_ID (allows removal of File_Name, Image and Thumbnail columns)
Generate System_Name for each product, update database
<cfquery name="GetProducts" datasource="CSAC_Dev">
	select *
	from product
</cfquery>

<cfset maxLen = 0>
<cfoutput query="GetProducts">
	<cfset sFileName = Replace(Replace(Replace(Replace(REReplace(product, "[^0-9A-Za-z /-]", "", "all"), "/", "_", "all"), " ", "_", "all"), "_-_", "_", "all"), "__", "_", "all")>
	<cfset sID = REReplace(ID, "[^0-9A-Za-z-.]", "", "all")>
	<cfif Len(sID) gt 0>
		<cfset token = sID>
	<cfelse>
		<cfset token = Product_ID>
	</cfif>
	<cfset fFileName = sFileName & "." & token>
	<!--- <cfif Len(File_Name) gt 0>
		<cfset ext = ListLast(File_Name, ".")>
		#fFileName#.#ext#<br>
	</cfif> --->
	<cfif Len(File_Name) gt 0 and FileExists(ExpandPath("/csac/common/files/") & File_Name)>
		<cfset dir = ExpandPath("/csac/common/files/")>
		<cfset fileName = dir & File_Name>
		<cfset newFileName = Product_ID & ".1." & ListLast(File_Name, ".")>
		<cfset destination = dir & newFileName>
		<cffile action="rename" source="#fileName#" destination="#destination#">
		<cfquery name="UpdateFile" datasource="CSAC_Dev">
			update product
			set File_Name = '#newFileName#'
			where Product_ID = #Product_ID#
		</cfquery>
		<h3>File Updated</h3>
		#fileName# > #destination#<br />
	<cfelseif Len(File_Name) gt 0>
		<h1>File not found - #Product_ID#</h1>
		<!--- <h2>#FileExists(ExpandPath("/csac/common/files/") & File_Name)#</h2> --->
	</cfif>
	<cfif Len(Image) gt 0 and FileExists(ExpandPath("/csac/common/images/products/") & Image)>
		<cfset dir = ExpandPath("/csac/common/images/products/")>
		<cfset fileName = dir & Image>
		<cfset newFileName = Product_ID & ".1." & ListLast(Image, ".")>
		<cfset destination = dir & newFileName>
		<cffile action="rename" source="#fileName#" destination="#destination#">
		<cfquery name="UpdateImage" datasource="CSAC_Dev">
			update product
			set [Image] = '#newFileName#'
			where Product_ID = #Product_ID#
		</cfquery>
		<h3>Image Updated</h3>
		#fileName# > #destination#<br />
	<cfelseif Len(Image) gt 0>
		<h1>Image not found - #Product_ID#</h1>
	</cfif>
	<cfif Len(Thumbnail) gt 0 and FileExists(ExpandPath("/csac/common/images/products/thumbnails/") & Thumbnail)>
		<cfset dir = ExpandPath("/csac/common/images/products/thumbnails/")>
		<cfset fileName = dir & Thumbnail>
		<cfset newFileName = Product_ID & ".1." & ListLast(Thumbnail, ".")>
		<cfset destination = dir & newFileName>
		<cffile action="rename" source="#fileName#" destination="#destination#">
		<cfquery name="UpdateThumbnail" datasource="CSAC_Dev">
			update product
			set Thumbnail = '#newFileName#'
			where Product_ID = #Product_ID#
		</cfquery>
		<h3>Thumbnail Updated</h3>
		#fileName# > #destination#<br />
	<cfelseif Len(Thumbnail) gt 0>
		<h1>Thumbnail not found - #Product_ID#</h1>
	</cfif>
	<cfquery name="InsertSystemName" datasource="CSAC_Dev">
		update Product
		set System_Name = '#fFileName#'
		where Product_ID = #Product_ID#
	</cfquery>
	<cfif Len(fFileName) gt maxLen>
		<cfset maxLen = Len(fFileName)>
	</cfif>
</cfoutput>

<cfoutput>
	<h1>#maxLen#</h1>
</cfoutput> --->

